﻿using Dapper;
using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace FRMS.DAL
{
    public class OwnerDAL
    {
        public bool AddOwner(OwnerInfo ownerInfo)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData)VALUES('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}')",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, ownerInfo.ParentID, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.UUID, ownerInfo.VirtualCard, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ownerInfo.projectID,ownerInfo.PhotoData);
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public int AddOwner_1(OwnerInfo ownerInfo)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID)VALUES('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11});select @@IDENTITY;",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, ownerInfo.ParentID, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.UUID, ownerInfo.VirtualCard, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ownerInfo.projectID);
            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public int ImportOwner(OwnerInfo ownerInfo)
        {
            string strSql = string.Empty;
            if (string.IsNullOrEmpty(ownerInfo.RoomName))
            {
                strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,UUID,projectID,CertificateNO,Phone,CardNO,VirtualCard,PicturePath,PhotoData)VALUES('{0}',(SELECT ID FROM owner_type WHERE OwnerTypeName='{1}' AND IsDelete=0),0,0,'{4}',{5},'{6}','{7}','{8}',{9},'{10}','{11}');select @@IDENTITY;",
                                           ownerInfo.OwnerName, ownerInfo.OwnerTypeName, ownerInfo.BuildingName, ownerInfo.RoomName, ownerInfo.UUID, ownerInfo.projectID, ownerInfo.CertificateNO, ownerInfo.Phone, ownerInfo.CardNO, ownerInfo.VirtualCard, ownerInfo.PicturePath,ownerInfo.PhotoData);
            }
            else
            {
                strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,UUID,projectID,CertificateNO,Phone,CardNO,VirtualCard,PicturePath,PhotoData)VALUES('{0}',(SELECT ID FROM owner_type WHERE OwnerTypeName='{1}' AND IsDelete=0),(SELECT ID FROM vi_room WHERE BuildingName='{2}' AND RoomName='{3}' AND IsDelete=0 AND projectID={5}),0,'{4}',{5},'{6}','{7}','{8}',{9},'{10}','{11}');select @@IDENTITY;",
                                           ownerInfo.OwnerName, ownerInfo.OwnerTypeName, ownerInfo.BuildingName, ownerInfo.RoomName, ownerInfo.UUID, ownerInfo.projectID, ownerInfo.CertificateNO, ownerInfo.Phone, ownerInfo.CardNO, ownerInfo.VirtualCard, ownerInfo.PicturePath,ownerInfo.PhotoData);
            }

            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }


        public int AddProperty(OwnerInfo ownerInfo)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData)VALUES('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}');select @@IDENTITY;",
                                           ownerInfo.OwnerName, 2, 0, 0, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.UUID, ownerInfo.VirtualCard, Convert.ToDateTime(ownerInfo.MaxTime).ToString("yyyy-MM-dd HH:mm:ss"), ownerInfo.projectID,ownerInfo.PhotoData);
            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public int AddSyncOwner(SyncOwnerInfo ownerInfo, int projectID, string uuid)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData)VALUES('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}');select @@IDENTITY;",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, 0, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, "", uuid, 1, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), projectID,ownerInfo.PhotoData);
            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public bool UpdateSyncOwner(SyncOwnerInfo ownerInfo, int projectID)
        {
            string strSql = string.Format(@"update owner_info SET OwnerName='{0}',OwnerType={1},RoomID={2},CardNO='{3}',Phone='{4}',CertificateNO='{5}' WHERE UUID='{6}' AND projectID={7}",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.UUID, projectID);
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public int CopyOwner(int ownerid, int roomid)
        {
            string strSql = @"INSERT INTO owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData)SELECT OwnerName,OwnerType," + roomid + ",ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData FROM owner_info WHERE ID=" + ownerid + ";select @@IDENTITY;";
            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public int AddSlaveOwner(OwnerInfo ownerInfo)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,RoomID,ParentID,CardNO,Phone,CertificateNO,PicturePath,UUID,VirtualCard,MaxTime,projectID,PhotoData)VALUES('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}');select @@IDENTITY;",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, ownerInfo.ParentID, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.UUID, ownerInfo.VirtualCard, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), ownerInfo.projectID,ownerInfo.PhotoData);
            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public bool UpdateOwner(OwnerInfo ownerInfo)
        {
            if (string.IsNullOrEmpty(ownerInfo.MaxTime))
            {
                ownerInfo.MaxTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }

            string strSql = string.Format(@"UPDATE owner_info SET OwnerName='{0}',OwnerType={1},RoomID={2},ParentID={3},CardNO='{4}',Phone='{5}',CertificateNO='{6}',PicturePath='{7}',VirtualCard={9},MaxTime='{10}',PhotoData='{11}' WHERE ID={8}",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.RoomID, ownerInfo.ParentID, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.ID, ownerInfo.VirtualCard, Convert.ToDateTime(ownerInfo.MaxTime).ToString("yyyy-MM-dd HH:mm:ss"),ownerInfo.PhotoData);
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public bool DeleteOwner(OwnerInfo ownerInfo)
        {
            string strSql;
            if (ownerInfo.OwnerType == 4)
            {
                strSql = "Delete FROM owner_info WHERE ID=" + ownerInfo.ID;
            }
            else
            {
                strSql = "UPDATE owner_info SET IsDelete=1 WHERE ID=" + ownerInfo.ID;
            }
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public bool DeleteSlaveOwner(int pid)
        {
            string strSql = "UPDATE owner_info SET IsDelete=1 WHERE ParentID=" + pid;
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public List<OwnerInfo> QueryOwner(OwnerInfo ownerInfo)
        {
            string strSql = "SELECT * FROM vi_owner";
            string sqlwhere = string.Empty;

            if (ownerInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + ownerInfo.projectID + " AND";
            }
            if (ownerInfo.ParentID > 0)
            {
                sqlwhere += " ParentID=" + ownerInfo.ParentID + " AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CardNO))
            {
                sqlwhere += " CardNO like '%" + ownerInfo.CardNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + ownerInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.UUID))
            {
                sqlwhere += " UUID like '%" + ownerInfo.UUID + "%' AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            if (ownerInfo.Recordhead > -1 && ownerInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + ownerInfo.Recordhead + "," + ownerInfo.Recordtail;
            }

            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }

        public int QueryOwnerCount(OwnerInfo ownerInfo)
        {
            string strSql = "SELECT COUNT(1) FROM vi_owner";
            string sqlwhere = string.Empty;

            if (ownerInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + ownerInfo.projectID + " AND";
            }
            if (ownerInfo.ParentID > 0)
            {
                sqlwhere += " ParentID=" + ownerInfo.ParentID + " AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CardNO))
            {
                sqlwhere += " CardNO like '%" + ownerInfo.CardNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + ownerInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.UUID))
            {
                sqlwhere += " UUID like '%" + ownerInfo.UUID + "%' AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            return SqlHelper<OwnerInfo>.SqlScalar(strSql);
        }

        public List<OwnerInfo> QueryOwnerList(OwnerInfo ownerInfo)
        {
            string sqlwhere = string.Empty;
            string strSql = "SELECT * FROM vi_owner";
            if (ownerInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + ownerInfo.projectID + " AND";
            }
            if (ownerInfo.RoomID > 0)
            {
                sqlwhere += " RoomID=" + ownerInfo.RoomID + " AND";
            }
            if (ownerInfo.OwnerType > 0)
            {
                sqlwhere += " OwnerType=" + ownerInfo.OwnerType + " AND";
            }
            if (ownerInfo.OwnerType == 0)
            {
                sqlwhere += " OwnerType !=4 AND";
            }
            if (ownerInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + ownerInfo.BuildID + " AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CardNO))
            {
                sqlwhere += " CardNO like '%" + ownerInfo.BuildID + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CertificateNO))
            {
                sqlwhere += " CertificateNO like '%" + ownerInfo.CertificateNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + ownerInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + ownerInfo.BuildingName + "%' AND";
            }
            sqlwhere += " IsDelete=0 AND";
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }

            if (!string.IsNullOrEmpty(ownerInfo.BuildingName))
            {
                strSql = string.Format(@"SELECT * from ({0} UNION SELECT * FROM vi_owner WHERE OwnerType=2 AND IsDelete=0 AND projectID={1}) AS b", strSql, ownerInfo.projectID);
            }

            /*string strSql = string.Format(@"SELECT
		                                                *
	                                                FROM
		                                                vi_owner
	                                                WHERE
		                                                {0}
	                                                 ParentID = 0
                                                     AND IsDelete=0
                                                UNION 
	
		                                                SELECT
			                                                *
		                                                FROM
			                                                vi_owner
		                                                WHERE
			                                                ID IN (
				                                                SELECT
					                                                ParentID
				                                                FROM
					                                                vi_owner
				                                                WHERE
					                                                {0}
				                                                 ParentID != 0 AND IsDelete=0
			                                                )", sqlwhere);*/

            if (ownerInfo.Recordhead > -1 && ownerInfo.Recordtail > 0)
            {
                strSql += " LIMIT " + ownerInfo.Recordhead + "," + ownerInfo.Recordtail;
            }

            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }

        public int QueryOwnerListCount(OwnerInfo ownerInfo)
        {
            string sqlwhere = string.Empty;
            string strsql;
            if (ownerInfo.projectID > 0)
            {
                sqlwhere += " projectID=" + ownerInfo.projectID + " AND";
            }
            if (ownerInfo.RoomID > 0)
            {
                sqlwhere += " RoomID=" + ownerInfo.RoomID + " AND";
            }
            if (ownerInfo.OwnerType > 0)
            {
                sqlwhere += " OwnerType=" + ownerInfo.OwnerType + " AND";
            }
            if (ownerInfo.OwnerType == 0)
            {
                sqlwhere += " OwnerType !=4 AND";
            }
            if (ownerInfo.BuildID > 0)
            {
                sqlwhere += " BuildID=" + ownerInfo.BuildID + " AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CardNO))
            {
                sqlwhere += " CardNO like '%" + ownerInfo.BuildID + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.CertificateNO))
            {
                sqlwhere += " CertificateNO like '%" + ownerInfo.CertificateNO + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.OwnerName))
            {
                sqlwhere += " OwnerName like '%" + ownerInfo.OwnerName + "%' AND";
            }
            if (!string.IsNullOrEmpty(ownerInfo.BuildingName))
            {
                sqlwhere += " BuildingName like '%" + ownerInfo.BuildingName + "%' AND";
            }
            sqlwhere += " IsDelete =0 AND";

            if (string.IsNullOrEmpty(ownerInfo.BuildingName))
            {
                strsql = "SELECT COUNT(1) from vi_owner" + " where" + sqlwhere.Substring(0, sqlwhere.Length - 4);
            }
            else
            {
                strsql = string.Format(@"SELECT COUNT(1) from (SELECT * FROM vi_owner WHERE {0} UNION SELECT * FROM vi_owner WHERE OwnerType=2 AND IsDelete=0 AND projectID={1}) AS b", sqlwhere.Substring(0, sqlwhere.Length - 4), ownerInfo.projectID);
            }

            /*string strSql = string.Format(@"SELECT COUNT(1) FROM(
	                                                SELECT
		                                                *
	                                                FROM
		                                                vi_owner
	                                                WHERE
		                                                {0}
	                                                 ParentID = 0 AND IsDelete=0
                                                UNION 
		                                                SELECT
			                                                *
		                                                FROM
			                                                vi_owner
		                                                WHERE
			                                                ID IN (
				                                                SELECT
					                                                ParentID
				                                                FROM
					                                                vi_owner
				                                                WHERE
					                                                {0}
                                                                 ParentID != 0 AND IsDelete=0
			                                                )
	                                                ) as a", sqlwhere);*/

            return SqlHelper<OwnerInfo>.SqlScalar(strsql);
        }

        public List<OwnerType> QueryOwnerMasterType()
        {
            string strSql = "SELECT * FROM owner_type WHERE ID=1 or ID=2";

            return SqlHelper<OwnerType>.SqlList(strSql);
        }
        public List<OwnerType> QueryOwnerSlaveType()
        {
            string strSql = "SELECT * FROM owner_type WHERE ID=1 or ID=3";

            return SqlHelper<OwnerType>.SqlList(strSql);
        }

        public OwnerInfo LoadOwnerInfo(int id)
        {
            string strSql = "SELECT * FROM vi_owner WHERE ID=" + id;
            return SqlHelper<OwnerInfo>.SqlSingleData(strSql);
        }

        public bool CheckUUID(string uuid)
        {
            string strSql = "SELECT COUNT(1) FROM owner_info WHERE UUID='" + uuid + "' AND IsDelete=0";
            int count = SqlHelper<OwnerInfo>.SqlScalar(strSql);

            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool CheckCard(string cardid)
        {
            string strSql = "SELECT COUNT(1) FROM owner_info WHERE CardNO='" + cardid + "'";
            int count = SqlHelper<OwnerInfo>.SqlScalar(strSql);

            if (count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public List<OwnerInfo> QueryOwnerByPID(int parentid)
        {
            string strSql = "SELECT * FROM vi_owner where IsDelete=0 AND ParentID=" + parentid;

            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }

        public List<OwnerChildInfo> QueryOwnerChildByPID(int parentid)
        {
            string strSql = "SELECT * FROM vi_owner where IsDelete=0 AND ParentID=" + parentid;

            return SqlHelper<OwnerChildInfo>.SqlList(strSql);
        }

        public bool UpdateAllByOwnerid(VoucherInfo voucher, bool CarduploadFlag, bool PicuploadFlag, bool Lssuestate)
        {
            string sqlwhere = string.Empty;
            if (CarduploadFlag)
            {
                sqlwhere += "CarduploadFlag=0,";
            }
            if (PicuploadFlag)
            {
                sqlwhere += "PicuploadFlag=0,";
            }
            if (Lssuestate)
            {
                sqlwhere += "Lssuestate=0,";
            }
            if (voucher.OwnerType == 2)
            {
                sqlwhere += "AuthData='" + voucher.AuthData + "',";
            }
            if (!string.IsNullOrEmpty(voucher.EffectiveEndtime))
            {
                sqlwhere += "EffectiveEndtime='" + voucher.EffectiveEndtime + "',";
            }
            if (sqlwhere.Length > 0)
            {
                string strSql = "UPDATE voucher_info SET " + sqlwhere.Substring(0, sqlwhere.Length - 1) + " WHERE OwnerID=" + voucher.OwnerID;
                return SqlHelper<VoucherInfo>.SqlExecute(strSql);
            }
            else
            {
                return true;
            }
        }

        public bool JLUpdateOwnerLssuestate(VoucherInfo voucher)
        {
            string strSql = "UPDATE voucher_info SET Lssuestate=0 WHERE OwnerID=" + voucher.ID;
            return SqlHelper<VoucherInfo>.SqlExecute(strSql);
        }

        public bool UpdateOwner_1(OwnerInfo ownerInfo)
        {
            if (string.IsNullOrEmpty(ownerInfo.MaxTime))
            {
                ownerInfo.MaxTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }
            string strSql = string.Format(@"UPDATE owner_info SET OwnerName='{0}',OwnerType={1},CardNO='{2}',Phone='{3}',CertificateNO='{4}',PicturePath='{5}',VirtualCard={7} WHERE ID={6}",
                                           ownerInfo.OwnerName, ownerInfo.OwnerType, ownerInfo.CardNO, ownerInfo.Phone, ownerInfo.CertificateNO, ownerInfo.PicturePath, ownerInfo.ID, ownerInfo.VirtualCard);
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public bool DeleteSyncOwner(string uuid, int projectID)
        {
            string strSql = "DELETE FROM owner_info WHERE projectID=" + projectID + " AND UUID='" + uuid + "'";
            return SqlHelper<OwnerInfo>.SqlDelete(strSql);
        }

        public List<OwnerInfo> QuerySyncOwner(int projectID,string uuid)
        {
            string strSql = "SELECT * FROM vi_owner where IsDelete=0 AND projectID=" + projectID+ " AND UUID='"+uuid+"' AND IsDelete=0";

            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }
    }
}
